/*
 * Local Projects Database
 * (c) 2003 by eRomania Gateway
 * Created on Jan 15, 2004
 *
 */
package ro.gateway.aida.usr.messaging;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.DBWrapable;
import ro.gateway.aida.db.PersistenceToken;



/**
 * MessageDB
 * @author Mihai Postelnicu <p>
 *  mihai@ro-gateway.org
 */
public class MessageDB extends DBPersistenceManager implements DBWrapable
{
      private MessageDB( PersistenceToken token )
      {
            super( token );
      }

      public static MessageDB getManager( PersistenceToken token )
      {
            return new MessageDB( token );
      }

      public void synchronize( Object o ) throws Exception
      {
            Message m = ( Message ) o;

            if ( m.getId() == -1 )
                  add( m );
            else
                  update( m );
      }

      public void delete( Object o ) throws Exception
      {
            Message m = ( Message ) o;
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( DELETE );
                  ps.setLong( 1, m.getId() );
                  ps.executeUpdate();
                  ps.close();
            }
            finally
            {
                  con.close();
            }
      }

      protected void update( Object o ) throws Exception
      {
            Message m = ( Message ) o;
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( UPDATE );
                  ps.setString( 1, m.getSubject() );
                  ps.setString( 2, m.getBody() );
                  ps.setString( 3, bool2Str( m.isTxt() ) );
                  ps.setDate( 4, new Date( System.currentTimeMillis() ) );
                  ps.setLong( 5, m.getId() );
                  ps.executeUpdate();
                  ps.close();
            }
            finally
            {
                  con.close();
            }
      }

      public Object get( Object o ) throws Exception
      {
            Message m = ( Message ) o;
            if ( m.getId() != -1 ) return getById( o );
            if ( m.getUser_id() != -1 ) return getForUser( m );

            //default
            return null;
      }

      public Object getById( Object o ) throws Exception
      {
            Message m = ( Message ) o;
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( GET );
                  ps.setLong( 1, m.getId() );

                  ResultSet rs = ps.executeQuery();

                  if ( rs.next() )
                  {
                        m.setId( m.getId() );
                        m.setSubject( rs.getString( 1 ) );
                        m.setBody( rs.getString( 2 ) );
                        m.setUser_id( rs.getLong( 3 ) );
                        m.setTxt( str2Bool( rs.getString( 4 ) ) );
                        m.setModified( rs.getDate( 5 ) );
                  }
                  else
                        m.setId( -1 );

                  rs.close();
                  ps.close();
            }
            finally
            {
                  con.close();
            }

            return m;
      }

      protected void add( Object o ) throws Exception
      {
            Message m = ( Message ) o;
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( ADD );
                  ps.setLong( 1, m.getUser_id() );
                  ps.setString( 2, m.getSubject() );
                  ps.setString( 3, m.getBody() );
                  ps.setString( 4, bool2Str( m.isTxt() ) );
                  ps.setDate( 5, new Date( System.currentTimeMillis() ) );

                  ps.executeUpdate();
                  ps.close();
            }
            finally
            {
                  con.close();
            }
      }

      protected List getForUser( Message m ) throws Exception
      {
            Connection con = getConnection();
            ArrayList ret = new ArrayList();

            try
            {
                  PreparedStatement ps = con.prepareStatement( GET_FOR_USER );
                  ps.setLong( 1, m.getUser_id() );

                  ResultSet rs = ps.executeQuery();

                  while ( rs.next() )
                  {
                        Message t = new Message( rs.getLong( 1 ), m.getUser_id() );
                        t.setSubject( rs.getString( 2 ) );
                        t.setBody( rs.getString( 3 ) );
                        t.setTxt( str2Bool( rs.getString( 4 ) ) );
                        t.setModified( rs.getDate( 5 ) );
                        ret.add( t );
                  }

                  rs.close();
                  ps.close();
            }
            finally
            {
                  con.close();
            }

            return ret;
      }

      public static final String ADD = "INSERT INTO messages (user_id,subject,body,txt,modified) VALUES (?,?,?,?,?)";
      public static final String UPDATE = "UPDATE messages SET subject=?, body=?, txt=?, modified=? WHERE id=?";
      public static final String GET = "SELECT subject,body,user_id,txt,modified FROM messages WHERE id=?";
      public static final String GET_FOR_USER = "SELECT id,subject,body,txt,modified FROM messages WHERE user_id=?";
      public static final String DELETE = "DELETE FROM messages WHERE id=?";
}
